﻿/*Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。
例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+
*/ 

create table EmpSalary
(
  id int primary key not null identity,
  name nvarchar(80),
  salary int,
  departmentId int
)
go 
create table Department
(
   id int primary key not null identity,
   name nvarchar(80)
)

insert into EmpSalary(name, salary, departmentId) values('Joe',70000,1)
insert into EmpSalary(name, salary, departmentId) values('Henry',80000,2)
insert into EmpSalary(name, salary, departmentId) values('Sam',60000,2)
insert into EmpSalary(name, salary, departmentId) values('Max',90000,1)

insert into Department values ('IT')
insert into Department values ('Sales')

select departmentId,max(salary) as max_salary
from EmpSalary  group by departmentId 

select a.name as emp_name ,a.salary,b.name as dep_name 
from (select max(salary) as salary, departmentId --找出最大值
from EmpSalary
group by departmentId ) as t--建立临时表t
full join EmpSalary a on t.departmentId=a.departmentId
full join Department b on b.id=t.departmentId--建立全连接
where a.salary=t.salary 